/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.gob.pnp.despachadores;

import java.sql.*;
import java.util.*;
import java.util.ArrayList;
import oracle.jdbc.*;
import pe.gob.pnp.actions.*;
import pe.gob.pnp.factories.*;

/**
 *
 * @author Gregorovichz Carlos Rossi
 * @date   31/03/2010
 */
public class PersonaDespachador {

    public static List<Persona> obtenerPersona() throws SQLException {
        List<Persona> lstPersona = new ArrayList<Persona>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_OBTENER_PERSONAL(?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, OracleTypes.CURSOR);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(1);
            while (rs.next()) {
                Persona persona = new Persona();
                persona.setNpersIdentificador(rs.getInt("NPERS_IDENTIFICADOR"));
                persona.setCpersApepaterno(rs.getString("CPERS_APEPATERNO"));
                persona.setCpersApematerno(rs.getString("CPERS_APEMATERNO"));
                persona.setCpersNombre(rs.getString("CPERS_NOMBRE"));
                persona.setBpersTitular(rs.getBoolean("BPERS_TITULAR"));
                persona.setNpersTitular(rs.getInt("NPERS_TITULAR"));
                lstPersona.add(persona);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstPersona;
    }

    public static List<PersonaBean> obtenerFiltrarPersona(PersonaBean parametros) throws SQLException {
        List<PersonaBean> lstPersona = new ArrayList<PersonaBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_FILTRAR_PERSONAL(?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setString(1, parametros.getCpersApepaterno().toUpperCase());
            cs.setString(2, parametros.getCpersApematerno().toUpperCase());
            cs.setString(3, parametros.getCpersNombre().toUpperCase());
            cs.registerOutParameter(4, OracleTypes.CURSOR);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(4);
            while (rs.next()) {
                PersonaBean persona = new PersonaBean();
                persona.setNpersIdentificador(rs.getInt("NPERS_IDENTIFICADOR"));
                persona.setCpersApepaterno(rs.getString("CPERS_APEPATERNO").toUpperCase());
                persona.setCpersApematerno(rs.getString("CPERS_APEMATERNO").toUpperCase());
                persona.setCpersNombre(rs.getString("CPERS_NOMBRE").toUpperCase());
                persona.setBpersTitular(rs.getBoolean("BPERS_TITULAR"));
                persona.setNpersTitular(rs.getInt("NPERS_TITULAR"));
                persona.setCpers_codofin(rs.getString("CPERS_CODOFIN"));
                persona.setCpers_nrocip(rs.getString("CPERS_NROCIP"));
                persona.setCpers_unidad(rs.getString("CPERS_UNIDAD"));
                persona.setCpers_nrodni(rs.getString("CPERS_NRODNI"));
                persona.setCgrad_descripcion(rs.getString("CGRAD_DESCRIPCION"));
                lstPersona.add(persona);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstPersona;
    }

    public static List<PersonaBean> obtenerFiltrarPersonaExpediente(PersonaBean parametros) throws SQLException {
        List<PersonaBean> lstPersona = new ArrayList<PersonaBean>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();

            String sql = "SELECT P.NPERS_IDENTIFICADOR, P.CPERS_APEPATERNO, P.CPERS_APEMATERNO,";
            sql += " P.CPERS_NOMBRE,P.BPERS_TITULAR,P.NPERS_TITULAR,P.CPERS_CODOFIN,";
            sql += " P.CPERS_UNIDAD,P.CGRAD_DESCRIPCION,P.CPERS_NRODNI,P.CPERS_NROCIP,P.CPERS_SEXO,";
            sql += " NVL(E.NEXPE_IDENTIFICADOR,0) AS NEXPE_IDENTIFICADOR,NVL(E.CEXPE_CODIGOARCHIVO,'S/N') AS CEXPE_CODIGOARCHIVO";
            sql += " FROM PERMA_PERSONA P";
            sql += " LEFT JOIN PERMA_PERSONALPOLICIAL PP";
            sql += " ON P.NPERS_IDENTIFICADOR = PP.NPERS_IDENTIFICADOR";
            sql += " LEFT JOIN SISMA_EXPEDIENTE E";
            sql += " ON E.NPEPO_IDENTIFICADOR = PP.NPEPO_IDENTIFICADOR";
            sql += " WHERE P.BPERS_ELIMINADO = 0   ";
            sql += " AND UPPER(P.CPERS_APEPATERNO) LIKE NVL(UPPER(?),UPPER(P.CPERS_APEPATERNO)) || '%'";
            sql += " AND UPPER(P.CPERS_APEMATERNO) LIKE NVL(UPPER(?),UPPER(P.CPERS_APEMATERNO)) || '%'";
            sql += " AND UPPER(P.CPERS_NOMBRE) LIKE NVL(UPPER(?),UPPER(P.CPERS_NOMBRE)) || '%'";
            sql += " AND P.CPERS_NRODNI = NVL(UPPER(?),UPPER(P.CPERS_NRODNI))";
            sql += " AND P.CPERS_NROCIP = NVL(UPPER(?),UPPER(P.CPERS_NROCIP))";
            sql += " ORDER BY P.CPERS_APEPATERNO,P.CPERS_APEMATERNO,P.CPERS_NOMBRE";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, (parametros.getCpersApepaterno() == null || parametros.getCpersApepaterno().equals("")) ? null : parametros.getCpersApepaterno().toUpperCase());
            ps.setString(2, (parametros.getCpersApematerno() == null || parametros.getCpersApematerno().equals("")) ? null : parametros.getCpersApematerno().toUpperCase());
            ps.setString(3, (parametros.getCpersNombre() == null || parametros.getCpersNombre().equals("")) ? null : parametros.getCpersNombre().toUpperCase());
            ps.setString(4, (parametros.getCpers_nrodni() == null || parametros.getCpers_nrodni().equals("")) ? null : parametros.getCpers_nrodni());
            ps.setString(5, (parametros.getCpers_nrocip()== null || parametros.getCpers_nrocip().equals("")) ? null : parametros.getCpers_nrocip());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                PersonaBean persona = new PersonaBean();
                persona.setNpersIdentificador(rs.getInt("NPERS_IDENTIFICADOR"));
                persona.setCpersApepaterno(rs.getString("CPERS_APEPATERNO").toUpperCase());
                persona.setCpersApematerno(rs.getString("CPERS_APEMATERNO").toUpperCase());
                persona.setCpersNombre(rs.getString("CPERS_NOMBRE").toUpperCase());
                persona.setBpersTitular(rs.getBoolean("BPERS_TITULAR"));
                persona.setNpersTitular(rs.getInt("NPERS_TITULAR"));
                persona.setCpers_codofin(rs.getString("CPERS_CODOFIN"));
                persona.setCpers_nrocip(rs.getString("CPERS_NROCIP"));
                persona.setCpers_unidad(rs.getString("CPERS_UNIDAD"));
                persona.setCpers_nrodni(rs.getString("CPERS_NRODNI"));
                persona.setCgrad_descripcion(rs.getString("CGRAD_DESCRIPCION"));
                persona.setNexpeIdentificador(rs.getInt("NEXPE_IDENTIFICADOR"));
                persona.setCexpeCodigoArchivo(rs.getString("CEXPE_CODIGOARCHIVO"));
                lstPersona.add(persona);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstPersona;
    }

    public static PersonaBean obtenerPersonaUnica( int expediente ) throws SQLException {
        PersonaBean persona = null;
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_OBTENER_PERSONAL_UNICO(?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, expediente);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(2);
            while (rs.next()) {
                persona = new PersonaBean();
                persona.setNpersIdentificador(rs.getInt("NPERS_IDENTIFICADOR"));
                persona.setCpersApepaterno(rs.getString("CPERS_APEPATERNO").toUpperCase());
                persona.setCpersApematerno(rs.getString("CPERS_APEMATERNO").toUpperCase());
                persona.setCpersNombre(rs.getString("CPERS_NOMBRE").toUpperCase());
                persona.setBpersTitular(rs.getBoolean("BPERS_TITULAR"));
                persona.setNpersTitular(rs.getInt("NPERS_TITULAR"));
                persona.setCpers_sexo(rs.getString("Cpers_sexo"));
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return persona;
    }

    //    Revisar este metodo ya que se duplica el mismo metodo en PersonalPolicial Despachador
    public static List<PersonalPolicial> obtenerPersonalPolicial() throws SQLException {
        List<PersonalPolicial> lstPersonalPolicial = new ArrayList<PersonalPolicial>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_OBTENER_PERSONAL_POLICIAL(?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, OracleTypes.CURSOR);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(1);
            while (rs.next()) {
                PersonalPolicial personalpolicial = new PersonalPolicial();
                personalpolicial.setNpersIdentificador(rs.getInt("NPERS_IDENTIFICADOR"));
                personalpolicial.setCpersApepaterno(rs.getString("CPERS_APEPATERNO"));
                personalpolicial.setCpersApematerno(rs.getString("CPERS_APEMATERNO"));
                personalpolicial.setCpersNombre(rs.getString("CPERS_NOMBRE"));
                personalpolicial.setNpepoIdentificador(rs.getInt("NPEPO_IDENTIFICADOR"));
                personalpolicial.setNpepoTiempoServicio(rs.getDouble("NPEPO_TIEMPOSERVICIO"));
                personalpolicial.setBpepoPerteneceCuadroMerito(rs.getBoolean("BPEPO_PERTENECECUADROMERITO"));
                personalpolicial.setDpepoFechaIngreso(rs.getDate("DPEPO_FECHAINGRESO"));
                personalpolicial.setDpepoFechaBaja(rs.getDate("DPEPO_FECHABAJA"));
                personalpolicial.setNgradIdentificador(rs.getInt("NGRAD_IDENTIFICADOR"));
                personalpolicial.setCdepeNombre(rs.getString("CDEPE_NOMBRE"));
                lstPersonalPolicial.add(personalpolicial);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstPersonalPolicial;
    }

    public static List<PersonalPolicial> buscarPersonalPolicial(PersonalPolicial parametros) throws SQLException {
        List<PersonalPolicial> lstPersonal = new ArrayList<PersonalPolicial>();
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_BUSCAR_PERSONAL_POLICIAL(?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setString(1, parametros.getCpersApepaterno());
            cs.setString(2, parametros.getCpersApematerno());
            cs.setString(3, parametros.getCpersNombre());
            cs.registerOutParameter(4, OracleTypes.CURSOR);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(4);
            while (rs.next()) {
                PersonalPolicial personal = new PersonalPolicial();
                personal.setNpepoIdentificador(rs.getInt("NPEPO_IDENTIFICADOR"));
                personal.setCpersApepaterno(rs.getString("CPERS_APEPATERNO"));
                personal.setCpersApematerno(rs.getString("CPERS_APEMATERNO"));
                personal.setCpersNombre(rs.getString("CPERS_NOMBRE"));
                personal.setNgradIdentificador(rs.getInt("NGRAD_IDENTIFICADOR"));
                lstPersonal.add(personal);
            }
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
        return lstPersonal;
    }

    public static void agregarPersona(Persona persona) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_AGREGAR_PERSONA(?,?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setString(1, persona.getCpersApepaterno());
            cs.setString(2, persona.getCpersApematerno());
            cs.setString(3, persona.getCpersNombre());
            cs.setInt(4, persona.isBpersTitular() ? 1 : 0);
            cs.setInt(5, persona.getNpersTitular());
            cs.execute();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static void modificarPersona(Persona persona) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_MODIFICAR_PERSONA(?,?,?,?,?,?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setString(1, persona.getCpersApepaterno());
            cs.setString(2, persona.getCpersApematerno());
            cs.setString(3, persona.getCpersNombre());
            cs.setInt(4, persona.isBpersTitular() ? 1 : 0);
            cs.setInt(5, persona.getNpersTitular());
            cs.setInt(6, persona.getNpersIdentificador());
            cs.execute();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }

    public static void eliminarPersona(int npareIdentificador) throws SQLException {
        Connection conn = null;
        try {
            conn = ConnectionPool.obtenerConexion();
            String sql = "{call SP_ELIMINAR_PERSONA(?)}";
            CallableStatement cs = conn.prepareCall(sql);
            cs.setInt(1, npareIdentificador);
            cs.execute();
        } finally {
            ConnectionPool.liberarConexion(conn);
        }
    }
}
